﻿using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

namespace BusinessLayer.cs
{
    public  class EmployeeBusinessLayer
    {
        public static IEnumerable<Employee> Employees
        {
            get
            {
                string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
                List<Employee> employees = new List<Employee>();
                using (SqlConnection con = new SqlConnection(connectionString))
                {
                    SqlCommand cmd = new SqlCommand("spListAllEmployees", con);
                    cmd.CommandType = CommandType.StoredProcedure;
                    con.Open();
                    SqlDataReader rdr = cmd.ExecuteReader();
                    while (rdr.Read())
                    {
                        Employee employee = new Employee();
                        employee.EmployeeId = Convert.ToInt32(rdr["EmployeeId"]);
                        employee.Name = rdr["Name"].ToString();
                        employee.Gender = rdr["Gender"].ToString();
                        employee.City = rdr["City"].ToString();
                        employee.DateOfBirth = Convert.ToDateTime(rdr["DateOfBirth"]);
                        employees.Add(employee);
                    }
                }
                return employees;
            }
        }

        public static void AddEmployee(Employee employee)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("spAddEmployee", con);
                cmd.CommandType = CommandType.StoredProcedure;
                SqlParameter paramName = new SqlParameter();
                paramName.ParameterName = "@Name";
                paramName.Value = employee.Name;
                cmd.Parameters.Add(paramName);

                SqlParameter paramGender = new SqlParameter();
                paramGender.ParameterName = "@Gender";
                paramGender.Value = employee.Gender;
                cmd.Parameters.Add(paramGender);

                SqlParameter paramCity = new SqlParameter();
                paramCity.ParameterName = "@City";
                paramCity.Value = employee.City;
                cmd.Parameters.Add(paramCity);

                SqlParameter paramDateOfBirth = new SqlParameter();
                paramDateOfBirth.ParameterName = "@DateOfBirth";
                paramDateOfBirth.Value = employee.DateOfBirth;
                cmd.Parameters.Add(paramDateOfBirth);
                con.Open();
                cmd.ExecuteNonQuery();

            }
        }

        public static void SaveEmployee(Employee employee)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("spSaveEmployee",con);
                cmd.CommandType = CommandType.StoredProcedure;

                SqlParameter idParameter = new SqlParameter() { ParameterName = "@Id", Value =  employee.EmployeeId};
                SqlParameter nameParameter = new SqlParameter() { ParameterName = "@Name", Value = employee.Name };
                SqlParameter genderParameter = new SqlParameter() { ParameterName = "@Gender", Value = employee.Gender };
                SqlParameter cityParameter = new SqlParameter() { ParameterName = "@City", Value = employee.City };
                SqlParameter dobParameter = new SqlParameter() { ParameterName = "@DateOfBirth", Value = employee.DateOfBirth };

                cmd.Parameters.Add(idParameter);
                cmd.Parameters.Add(nameParameter);
                cmd.Parameters.Add(genderParameter);
                cmd.Parameters.Add(cityParameter);
                cmd.Parameters.Add(dobParameter);

                con.Open();
                cmd.ExecuteNonQuery();
            }
        }

        public static void DeleteEmployee(int id)
        {
            string connectionString = ConfigurationManager.ConnectionStrings["DBCS"].ConnectionString;
            using (SqlConnection con = new SqlConnection(connectionString))
            {
                SqlCommand cmd = new SqlCommand("DeleteEmployee", con) {CommandType = CommandType.StoredProcedure};
                SqlParameter parameterId = new SqlParameter()
                {
                    ParameterName = "@id",
                    Value = id,
                };
                cmd.Parameters.Add(parameterId);
                con.Open();
                cmd.ExecuteNonQuery();

            }

        }
    }
}
      